using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Collections;
using System.Data.SqlClient;
using System.Text;
using System.Diagnostics;
using System.Resources;
using System.Reflection;

/*=====================================================================

  File:      DataAccess.cs	
  Summary:   Provides all persistent data functionality for HR Resume web service.
  Date:	     March 9, 2004

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.
  Copyright (C) Microsoft Corporation.  All rights reserved.

This source code is intended only as a supplement to Microsoft
Development Tools and/or on-line documentation.  See these other
materials for detailed information regarding Microsoft code samples.

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.

======================================================= */

namespace Microsoft.Samples.SqlServer
{
    /// <summary>
    /// Provides all persistent data functionality for HR Resume web service.
    /// Important: Never reuse an instance of DataAccess! Make a fresh instance for 
    /// each call.
    /// </summary>
    public class DataAccess
    {
        // The "pipe" to the server
        private SqlConnection conn = null;

        // What we are asking the server to do
        private SqlCommand cmd = null;

        // A reasonable limit on the most number of responses a 
        // user might wish to examine.
        private const int maxResults = 500;

        private const int languageCountEstimate = 20;

        private const string connectionString = "server=(local);database=AdventureWorks;integrated security=true";

        public DataAccess()
        {
            // Create the connection based on the desired connections string.
            // Windows Authentication is used so that 
            // this application does not have to deal with protecting passwords.
            conn = new SqlConnection(GetConnectionString());
            // Create the object which will be used to invoke some SQL or
            // a stored procedure on the database server.
            cmd = conn.CreateCommand();
        }

        ~DataAccess()
        {
            // If for some reason the connection has not be closed by
            // the time this object is being destroyed, then destroy it now.
            if (conn != null && conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn = null;
            }
        }

        private string GetConnectionString()
        {
            return connectionString;
        }


        //TODO: +++++ Authorization Checks!
        /// <summary>
        ///		Locates resumes based on specific critera.
        /// </summary>
        /// <param name="candidateName">Who sumbitted the resume</param>
        /// <param name="fromModifyDate">The earliest date the resume has changed</param>
        /// <param name="toModifyDate">The latest date the resume has changed</param>
        /// <param name="fullText">Words to search for in the resume</param>
        /// <param name="andResults">Whether to union or intersection the results</param>
        /// <param name="userName">Who is invoking the search</param>
        /// <returns>A dataset contain a summary of the resumes located</returns>
        public DataSet SearchResumes(string candidateName,
                                   DateTime fromModifyDate,
                                   DateTime toModifyDate, string fullText,
                                   bool andResults, int lcid, string userName)
        {
            Debug.Assert(conn != null,
                         "SearchResumes: reusing DataAccess object doesn't work.");

            DataSet results = new DataSet("SearchResults");
            try
            {
                // Construct a custom query based on the user's search criteria.  The search may or may 
                // not involve a full text search.
                StringBuilder sb = null;
                bool isClausePresent = false;

                // If the user specified text to search for, generate the 
                // full text portion of the query.
                if (fullText != String.Empty)
                {
                    sb = new StringBuilder(
                        "SELECT [KEY] AS JobCandidateID, dbo.ResumeFullName(JC.Resume) AS [Name], JC.ModifiedDate " +
                        "FROM CONTAINSTABLE(HumanResources.JobCandidate, Resume, @SearchText");

                    SqlParameter searchTextParam = new SqlParameter(
                        "@SearchText", SqlDbType.NVarChar, 2048);

                    searchTextParam.Value = fullText;
                    cmd.Parameters.Add(searchTextParam);

                    if (lcid > -1)
                    {
                        sb.Append(", LANGUAGE ");
                        sb.Append(lcid);
                    }
                    sb.Append(", ");
                    sb.Append(maxResults);
                    sb.Append(") JOIN HumanResources.JobCandidate AS JC ON [KEY] = JC.JobCandidateID");
                }
                else
                {
                    // Otherwise we'll just be doing a normal query over the 
                    // HumanResources.JobCandidate table
                    sb = new StringBuilder(
                        "SELECT JobCandidateID, dbo.ResumeFullName(Resume) AS [Name], ModifiedDate FROM HumanResources.JobCandidate ");
                }

                // If the user entered one or more dates or candidate name to search by
                if (candidateName != String.Empty ||
                    fromModifyDate > DateTime.MinValue ||
                    toModifyDate < DateTime.MaxValue)
                {
                    // There is more than just full text search, or there is 
                    // something other than full text search.
                    if (fullText != String.Empty && !andResults)
                        sb.Append(" UNION SELECT JobCandidateID, dbo.ResumeFullName(Resume) AS [Name], ModifiedDate FROM HumanResources.JobCandidate");

                    sb.Append(" WHERE ");

                    // If the user specified a candidate name to search for
                    if (candidateName != String.Empty)
                    {
                        // Add a subclause to the where clause to filter based on
                        // candidate name.  Be sure to use parameters to avoid
                        // injection attacks.
                        sb.Append("dbo.ResumeFullName(Resume) LIKE @CandidateName");

                        SqlParameter candidateNameParam = new SqlParameter(
                            "@CandidateName", SqlDbType.NVarChar, 250);

                        candidateNameParam.Value = candidateName;
                        cmd.Parameters.Add(candidateNameParam);
                        isClausePresent = true;
                    }

                    // If at least one date was specified...
                    if (fromModifyDate > DateTime.MinValue ||
                        toModifyDate < DateTime.MaxValue)
                    {
                        // If we already have another subclause in the where 
                        // clause, add the appropriate connector
                        if (isClausePresent)
                            sb.Append(andResults ? " AND " : " OR ");

                        string lowerClause = "";
                        string upperClause = "";

                        // If the user specified the from date, determine that 
                        // portion of the WHERE subclause.
                        if (fromModifyDate > DateTime.MinValue)
                        {
                            lowerClause = "ModifiedDate > @FromModify";

                            SqlParameter fromModifyParam = new SqlParameter(
                                "@FromModify", SqlDbType.DateTime);

                            fromModifyParam.Value = fromModifyDate;
                            cmd.Parameters.Add(fromModifyParam);
                        }

                        // If the user specified the to date, determine that 
                        // portion of the WHERE subclause.
                        if (toModifyDate < DateTime.MaxValue)
                        {
                            upperClause = "ModifiedDate < @ToModify";

                            SqlParameter toModifyParam = new SqlParameter(
                                "@ToModify", SqlDbType.DateTime);

                            toModifyParam.Value = toModifyDate;
                            cmd.Parameters.Add(toModifyParam);
                        }

                        // If both dates are specified add
                        // the correct range check to the query.
                        if (fromModifyDate > DateTime.MinValue &&
                            toModifyDate < DateTime.MaxValue)
                        {
                            sb.Append("(");
                            sb.Append(lowerClause);
                            sb.Append(" AND ");
                            sb.Append(upperClause);
                            sb.Append(")");
                        }
                        // Otherwise just add half a range check (the
                        // other range check will be an empty string).
                        else
                            sb.Append(lowerClause + upperClause);
                    } // At least one date was specified
                } // There is more than just full text search or something other than FTS.	

                cmd.CommandText = sb.ToString();

                SqlDataAdapter sda = new SqlDataAdapter(cmd);

                sda.Fill(results);
                results.Tables[0].TableName = "Results";
                return results;
            } // Try
            catch (System.Exception se)
            {
                DataTable errorTable = new DataTable("Errors");
                errorTable.Columns.Add("ErrorCode", Type.GetType("System.Int32"));
                errorTable.Columns.Add("ErrorMessage",
                                       Type.GetType("System.String"));
                if (se is SqlException)
                    errorTable.Rows.Add(((SqlException)se).Number,
                                        se.ToString());
                else
                    errorTable.Rows.Add(-1, se.ToString());

                results.Tables.Add(errorTable);
                return results;
            }
            finally
            {
                conn.Close();
                conn = null;
            }
        } // SearchResumes


        /// <summary>
        ///		Fetches the XML representation of the specified job candidate's resume.
        /// </summary>
        /// <param name="candidateID">Which job candidate to fetch the resume for</param>
        /// <param name="userName">Who is asking for the resume</param>
        /// <returns></returns>
        public string ResumeContents(int candidateID, string userName)
        {
            Debug.Assert(conn != null,
                         "ResumeContents: reusing DataAccess object doesn't work.");
            try
            {
                // Setup the command
                cmd.CommandText = "usp_ResumeContents";
                cmd.CommandType = CommandType.StoredProcedure;

                // Declare the parameters
                SqlParameter idParam = new SqlParameter("@CandidateID", SqlDbType.Int);

                idParam.Value = candidateID;
                cmd.Parameters.Add(idParam);

                SqlParameter loginParam = new SqlParameter("@LoginID",
                                                           SqlDbType.NVarChar,
                                                           50);

                loginParam.Value = userName;
                cmd.Parameters.Add(loginParam);

                //Setup the return value parameter to be nvarchar(max)
                SqlParameter resultParam = new SqlParameter("@Result",
                                                            SqlDbType.NVarChar,
                                                            -1);

                resultParam.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(resultParam);

                //Open the connection and invoke the stored procedure.
                conn.Open();
                cmd.ExecuteNonQuery();

                return (String)(resultParam.Value);
            }
            finally
            {
                conn.Close();
                conn = null;
            }
        }

        public int[] FullTextLanguages()
        {
            Debug.Assert(conn != null,
                         "FullTextLanguages: reusing DataAccess object doesn't work.");
            cmd.CommandText = "usp_FullTextLanguages";
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                conn.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    //Consider using generics instead
                    ArrayList resultList = new ArrayList(languageCountEstimate);

                    while (rdr.Read())
                    {
                        object lcid = rdr["lcid"];
                        if (lcid is int)
                        {
                            resultList.Add(lcid);
                        }
                    }

                    int[] result = new int[resultList.Count];

                    return (int[])resultList.ToArray(Type.GetType("System.Int32"));
                }
            }
            finally
            {
                conn.Close();
                conn = null;
            }
        }
    } // class DataAccess
} // Namespace Microsoft.Samples.SqlServer

